from django.shortcuts import render, get_object_or_404
from .models import StockBasic, StockInfo
import matplotlib.pyplot as plt
import io 
import urllib, base64
from django.db import connection

stock_info_dict=[]

def stock_list(request):
    stocks = StockBasic.objects.all()
    return render(request, 'stocks/stock_list.html', {'stocks': stocks})

#def stock_detail(request, ts_code):
#    stock = get_object_or_404(StockBasic, ts_code=ts_code)
#    stock_info = StockInfo.objects.filter(ts_code=ts_code)
#    return render(request, 'stocks/stock_detail.html', {'stock': stock, 'stock_info': stock_info})

def stock_chart(request, ts_code):
    # 使用原生 SQL 查询个股信息
    with connection.cursor() as cursor:
        cursor.execute(
            f"SELECT ts_code, trade_date, open, high, low, close, pre_close, `change`, pct_chg, vol, amount FROM `{ts_code}` ORDER BY trade_date")
        stock_info = cursor.fetchall()
    
    stock_info_dict = [
        {
            'ts_code': row[0],
            'trade_date': row[1],
            'open': row[2],
            'high': row[3],
            'low': row[4],
            'close': row[5],
            'pre_close': row[6],
            'change': row[7],
            'pct_chg': row[8],
            'vol': row[9],
            'amount': row[10],
        }
        for row in stock_info
    ]
    #stock_info = StockInfo.objects.filter(ts_code=ts_code).order_by('trade_date')
    
    dates = [info[1] for info in stock_info]
    closes = [info[5] for info in stock_info]
    
    plt.figure(figsize=(10, 6))
    plt.plot(dates, closes,marker='o')
    plt.xlabel('Date')
    plt.ylabel('Close Price')
    plt.title(f'{ts_code} Close Price Over Time')
    plt.grid(True)

    buffer = io.BytesIO()
    plt.savefig(buffer, format='png')
    plt.close()
    buffer.seek(0)
    #print(buffer.getvalue())
    uri = urllib.parse.quote(base64.b64encode(buffer.getvalue()))
    #print(uri)
    return render(request, 'stocks/stock_chart.html', {'chart': uri})




def stock_detail(request, ts_code):
    stock = get_object_or_404(StockBasic, ts_code=ts_code)
    
    # 使用原生 SQL 查询个股信息
    with connection.cursor() as cursor:
        cursor.execute(f"SELECT ts_code, trade_date, open, high, low, close, pre_close, `change`, pct_chg, vol, amount FROM `{ts_code}` ORDER BY trade_date")
        stock_info = cursor.fetchall()
    
    # 将结果转换为字典列表（可选）
    stock_info_dict = [
        {
            'ts_code': row[0],
            'trade_date': row[1],
            'open': row[2],
            'high': row[3],
            'low': row[4],
            'close': row[5],
            'pre_close': row[6],
            'change': row[7],
            'pct_chg': row[8],
            'vol': row[9],
            'amount': row[10],
        }
        for row in stock_info
    ]
    ##StockInfo.objects.bulk_create([StockInfo(**info) for info in stock_info_dict])

    return render(request, 'stocks/stock_detail.html', {'stock': stock, 'stock_info': stock_info_dict})
